"""
这一步将ebookinfo的所有信息解析到一张表中
该表与db3的字段结构相同 方便后期导入db3
"""
import json
import re
import sys
import time

from facade.loggerfacade import get_streamlogger
from facade.mysqlfacade import MysqlUtiles
from xjlibrary.our_file_dir import BaseDir

curPath = BaseDir.get_file_dir_absolute(__file__)
configfile = BaseDir.get_new_path(curPath, "db.ini")

keytransformdicts = {"port": "db_port", "host": "db_host", "passwd": "db_pw", "user": "db_user",
                     "db": "db_name",
                     "chartset": "db_charset"}

logger = get_streamlogger()
myutil = MysqlUtiles(configfile, "db", keytransformdicts=keytransformdicts, logger=logger)

# 处理插入数据库中的引号问题
dealSqlQuote_func = lambda text: text.replace(r"'", r"''").replace("\\", "\\\\") if isinstance(text, str) else text

listresultsql = []


def selectFromebookInfo():
    sql = "select * from ebookinfo where stat=0 limit 1000"
    rows = myutil.SelectFromDB(sql)
    if rows:
        return rows
    else:
        return None


# 获取当天日期
def getTodayDate():
    return time.strftime('%Y%m%d', time.localtime(time.time()))


def deal_rows(rows):
    for row in rows:
        title = row[0]
        url = row[1]
        print(url)
        doi = row[2]
        doi = doi.replace("DOI:", "").strip()
        jsonmsg = row[3]
        jsonmsg = jsonmsg.replace("\r\n", "").replace("\t", "")
        print(bytes(jsonmsg, encoding="utf-8"))
        dicts = json.loads(jsonmsg)
        # 作者
        author = dicts["author"]
        # 作者单位
        author_institution = dicts["author_institution"]
        author_institution = re.sub(r'\s+', " ", author_institution)
        # vol
        vol = dicts["vol"]
        vol = vol.replace("Volume", "").strip()
        date_created = dicts["date"]
        date_created = date_created.split(":")
        if len(date_created) == 2:
            date_created = date_created[1].strip()
            date_created = time.strftime("%Y%m%d", time.strptime(date_created, '%B %d, %Y'))
            date = date_created[:4]
        else:
            date_created = "19000000"
            date = "1900"
        isbn13 = dicts["isbn13"]
        isbn13 = isbn13.replace("ISBN13:", "").strip()
        eisbn = dicts["eisbn"]
        eisbn = eisbn.replace("eISBN:", "").strip()
        # 版权
        copyright = dicts["copyright"]
        # 赞助部门
        spon = dicts["spon"]
        substrings = dicts["substrings"]
        issn = dicts["issn"]
        eissn = dicts["eissn"]
        mid = url.replace("/isbn/","")
        lngid = "ACS_TS_" + mid
        rawid = mid
        provider = "acsbook"
        provider_subject = ""
        if issn == "0065-2393":
            provider_subject = "advances"
            # 从书名
            title_series = "Advances in Chemistry"
        elif issn == "0097-6156":
            provider_subject = "symposium"
            # 从书名
            title_series = "ACS Symposium Series"
        else:
            title_series = ""
            provider_subject=""


        language = "US"
        country = "EN"
        provider_url = provider + "@https://pubs.acs.org" + url

        type = 1
        medium = 2
        batch = getTodayDate() + "00"

        sql = "INSERT IGNORE INTO modify_title_info_zt (" \
              "`lngid`,`rawid`,`title`,`title_sub`,`identifier_doi`, `identifier_pisbn`, `identifier_eisbn`," \
              "`identifier_pissn`,`identifier_eissn`,`creator`,`creator_institution`,`publisher`,`date`," \
              "`date_created`,`volume`,`provider_subject`,`language`,`country`,`provider`,`provider_url`," \
              "`type`,`medium`,`batch`,`creator_release`,`title_series`)"

        sql = sql + "values ('%s','%s','%s','%s','%s'," \
                    "'%s','%s','%s','%s','%s'," \
                    "'%s','%s','%s','%s','%s'," \
                    "'%s','%s','%s','%s','%s'," \
                    "'%s','%s','%s','%s','%s')"
        valuelist = [lngid, rawid, title, substrings, doi, isbn13, eisbn, issn, eissn,
                     author, author_institution, copyright, date, date_created, vol, provider_subject,
                     language, country, provider, provider_url, type, medium, batch, spon, title_series]
        dealvaluelist = []
        for value in valuelist:
            dealvaluelist.append(dealSqlQuote_func(value))
        lngid, rawid, title, substrings, doi, isbn13, eisbn, issn, eissn, author, author_institution, copyright, date, date_created, vol, provider_subject, language, country, provider, provider_url, type, medium, batch, spon, title_series = dealvaluelist

        sql = sql % (lngid, rawid, title, substrings, doi, isbn13, eisbn, issn, eissn,
                     author, author_institution, copyright, date, date_created, vol, provider_subject,
                     language, country, provider, provider_url, type, medium, batch, spon,title_series)
        print(sql)
        listresultsql.append(sql)
        sql = "update `ebookinfo` set stat=1 where url='{}'".format(url)
        listresultsql.append(sql)
        if len(listresultsql) >= 100:
            myutil.ExeSqlListToDB(listresultsql, errExit=True)
            listresultsql.clear()


if __name__ == "__main__":
    while True:
        rows = selectFromebookInfo()
        if rows:
            deal_rows(rows)
            if len(listresultsql) >= 0:
                myutil.ExeSqlListToDB(listresultsql, errExit=True)
                listresultsql.clear()
        else:
            break
